const Koa = require('koa');
const router = require('koa-router')();
const Cors = require('koa-cors');
const BodyParser = require('koa-bodyparser');
const mysql = require('mysql');
const app = new Koa();

const con = mysql.createConnection({
    port: '3306',
    host: "localhost",
    user: "root",
    password: "521369",
    database: "data2"
})
con.connect()
function query(sql) {
    return new Promise((resolve, reject) => {
        con.query(sql, (err, data) => {
            if (err) {
                reject(err)
            } else {
                resolve(data)
            }
        })
    })
}
//列表
router.get('/api/list', async (ctx) => {
    const { pageIndex, pageSize, keyword, sortBy, sortType, id } = ctx.query;
    let sql = `select * from blogs where 1=1 `

    let sqlCount = `select count(id) as total from blogs where 1=1 `
    if (id) {
        sql += `and id=${id} `
        sqlCount += `and id=${id} `
    }
    if (keyword) {
        sql += `and title like "%${keyword}%" `
        sqlCount += `and title like "%${keyword}%" `
    }
    if(sortBy && sortType) {
        sql += `order by ${sortBy} ${sortType} `
    }
    if(pageIndex &&pageSize) {
        sql += `limit ${(pageIndex-1)*pageSize},${pageSize}`
    }
    const list = await query(sql);
    const count = await query(sqlCount)
    ctx.body = {
        code: 1,
        data: {
            list,
            total: count[0].total
        }
    }
})
//添加
router.post('/api/list', async (ctx) => {
    const { title, names }=ctx.request.body;
    const sql=`insert into blogs(title,names,times) values("${title}","${names}","${Data.now()}")`;
    const data=await query(sql);
    if(data.affectedRows===1){
        ctx.body={
            code:1,
            data:{
                id:data.insertId
            },
            message:"添加成功"
        }
    }else{
        ctx.body={
            code:-1,
            message:'添加失败'
        }
    }
})
//编辑
router.put('/api/list',async (ctx)=>{
    
})
app.use(Cors());
app.use(BodyParser());
app.use(router.routes());
app.listen(8888, () => {
console.log(8888);
})